﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Configuration;


namespace ZenHR.AttData.DAL.DB3Helper
{
    public class SqliteAccess
    {

        private string connectionString;
        public string ConnectionString { get => connectionString; set => connectionString = value; }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="p_table"></param>
        /// <returns></returns>
        public bool TableIsExists(string p_table)
        {
            SQLiteConnection _sqliteconnection = new SQLiteConnection(ConnectionString);
            _sqliteconnection.Open();
            SQLiteCommand cmd = new SQLiteCommand("SELECT COUNT(*) FROM sqlite_master where type='table' and name='" + p_table + "';", _sqliteconnection);
         
            if (0 == Convert.ToInt32(cmd.ExecuteScalar()))
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        /// <summary>
        /// 执行SQl语句ConnectionString
        /// </summary>-+
        /// <param name="Sql"></param>
        /// <returns></returns>
        public int ExecSql(string Sql)
        {
            SQLiteConnection _sqliteconnection = new SQLiteConnection(ConnectionString);
            try
            {

                _sqliteconnection.Open();
                SQLiteCommand cmd = new SQLiteCommand(Sql, _sqliteconnection);
                SQLiteTransaction dtr = _sqliteconnection.BeginTransaction();
                cmd.ExecuteNonQuery();
                dtr.Commit();
                return 1;
            }
            catch (Exception ex)
            {
                return 0;
            }
            finally
            {
                _sqliteconnection.Close();
            }
        }

        /// <summary>
        /// 取数据
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public  DataTable FetchData(string sql)
        {
            SQLiteConnection _sqliteconnection = new SQLiteConnection(ConnectionString);
            SQLiteDataAdapter sda = new SQLiteDataAdapter(sql, _sqliteconnection);
            try
            {
                DataTable dt = new DataTable();
                _sqliteconnection.Open();
                sda.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                _sqliteconnection.Close();
            }
        }

        public  DataSet FetchDataSet(string sql)
        {
            SQLiteConnection _sqliteconnection = new SQLiteConnection(ConnectionString);
            SQLiteDataAdapter sda = new SQLiteDataAdapter(sql, _sqliteconnection);

           
            try
            {
                DataSet _ds = new DataSet();
                _sqliteconnection.Open();
                sda.Fill(_ds);
                return _ds;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                _sqliteconnection.Close();
            }
        }
        /// <summary>
        /// 更新数据  删除 新增 修改
        /// </summary>
        /// <param name="_DataSet"></param>
        /// <param name="_callBack"></param>
        public  void UpdateData(DataTable _datatable, Action<bool, string> _callBack = null)
        {
            SQLiteConnection _sqliteconnection = new SQLiteConnection(ConnectionString);
            try
            {
                if (_datatable == null)
                {
                    _callBack(false, "the table is null, please check value !"); return;
                }
                //SQLiteCommand mycommand = new SQLiteCommand(cnn);
                SQLiteDataAdapter sda = new SQLiteDataAdapter(string.Format("select * from {0}", _datatable.TableName), _sqliteconnection);

                SQLiteCommandBuilder comb = new SQLiteCommandBuilder(sda);
                sda.InsertCommand = comb.GetInsertCommand();
                sda.UpdateCommand = comb.GetUpdateCommand();
                _sqliteconnection.Open();
                //sda.InsertCommand=
                sda.Update(_datatable);
                if (_callBack != null)
                    _callBack(true, string.Empty);
            }
            catch (Exception ex)
            {
                if (_callBack != null)
                    _callBack(false, ex.Message);
            }
            finally
            {
                _sqliteconnection.Close();
            }
        }

        /// <summary>
        /// sqlite批量数据插入方法
        /// </summary>
        /// <param name="insert_str">自定义插入语句</param>
        /// <param name="_para">插入字段参数映射</param>
        /// <param name="dtData">待插入真实数据</param>
        public  string BatchInsertData(string insert_str, List<string> _para, DataTable dtData)
        {
            SQLiteConnection con = new SQLiteConnection(ConnectionString);
            try
            {
                con.Open();

                using (SQLiteTransaction tran = con.BeginTransaction())
                {
                    try
                    {
                        using (SQLiteCommand command = new SQLiteCommand(insert_str, con))
                        {
                            foreach (DataRow drData in dtData.Rows)
                            {
                 
                                foreach (var item in _para)
                                {

                                    try
                                    {


                                        switch (item)
                                        {
                                            case "emp_code":
                                                if (drData[item].ToString().Contains("("))
                                                {
                                                    command.Parameters.Add(new SQLiteParameter("@" + item, drData[item].ToString().Split('(')[0].Trim('"')));
                                                }
                                                else
                                                {
                                                    command.Parameters.Add(new SQLiteParameter("@" + item, drData[item].ToString().Trim('"')));
                                                }
                                                break;
                                            case "check_time":
                                                command.Parameters.Add(new SQLiteParameter("@" + item, Convert.ToDateTime(drData[item].ToString().Trim('"'))));
                                                break;
                                            default:
                                                command.Parameters.Add(new SQLiteParameter("@" + item, drData[item].ToString().Trim('"')));
                                                break;
                                        }


                                       
                                    }
                                    catch (Exception)
                                    {

                                        command.Parameters.Add(new SQLiteParameter("@" + item, drData[item].ToString().Trim('"')));
                                    }

                                }

                                command.ExecuteNonQuery();
                                command.Parameters.Clear();
                            }
                        }
                        tran.Commit();
                    }
                    catch
                    {
                        tran.Rollback();
                        throw;
                    }
                }

                return "";
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
            finally
            {
                con.Close();
            }
        }


        /// <summary>  
        /// DataTable装换成实体类T  
        /// </summary>  
        /// <typeparam name="T"></typeparam>  
        /// <param name="dt"></param>  
        /// <returns></returns>  
        public List<T> DataTableToT<T>(DataTable dt)
        {
            List<T> lt = new List<T>();
            if (dt == null || dt.Rows.Count == 0)
            {
                return lt;
            }

            var startdt = DateTime.Now;

            Type objType = typeof(T);
            var Properties = objType.GetProperties();
            foreach (DataRow dr in dt.Rows)
            {
                object obj = System.Activator.CreateInstance(objType);//创建实例  
                foreach (System.Reflection.PropertyInfo pi in Properties)//遍历T类的所有属性  
                {
                    if (pi.PropertyType.IsPublic && pi.CanWrite && dt.Columns.Contains(pi.Name))//属性是否为public和可写  
                    {
                        try
                        {

                            var value = dr[pi.Name];
                            Type pType = Type.GetType(pi.PropertyType.FullName);//属性类型  
                            if (pType == typeof(DateTime?) && value != null && (value as DateTime?).HasValue)
                            {
                                DateTime tp_dt;

                                if (DateTime.TryParse(string.Format("{0}", value), out tp_dt))
                                {
                                    value = tp_dt;

                                    objType.GetProperty(pi.Name).SetValue(obj, value, null);//赋值  
                                }
                            }
                            else if (pType == typeof(decimal?) && value != null && (value as decimal?).HasValue)
                            {
                                decimal tp_dc;

                                if (decimal.TryParse(string.Format("{0}", value), out tp_dc))
                                {
                                    value = tp_dc;

                                    objType.GetProperty(pi.Name).SetValue(obj, value, null);//赋值  
                                }

                            }
                            else
                            {
                                try
                                {
                                    if (value != null)
                                    {
                                        objType.GetProperty(pi.Name).SetValue(obj, value, null);//赋值  
                                    }
                                }
                                catch// (Exception ex)
                                {
                                    //SystemHelp.WriteMSGToLog_System("SetValue：" + ex.Message);
                                }
                            }
                            //object value = Convert.ChangeType(dr[pi.Name], pType);//转换dt值为对应属性的类型  

                        }
                        catch// (Exception ex)
                        {
                            //SystemHelp.WriteMSGToLog_System("类转换异常：" + ex.Message);
                        }

                    }
                }
                lt.Add((T)obj);
            }


            var enddt = DateTime.Now;

            return lt;
        }

        /// <summary>
        /// 转换为String
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static string NullToString(object obj)
        {
            if (obj != null)
            {
                return obj.ToString();
            }
            else
            {
                return "";
            }
        }

        /// <summary>
        /// 数字和String转换
        /// </summary>
        /// <param name="i"></param>
        /// <returns></returns>
        public static string NumToString(int i)
        {
            if (i == 1)
            {
                return "一";
            }
            else if (i == 2)
            {
                return "二";
            }
            else if (i == 3)
            {
                return "三";
            }
            else if (i == 4)
            {
                return "四";
            }
            else if (i == 5)
            {
                return "五";
            }
            else
            {
                return "";
            }
        }

    }

}